﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace VietImage.Models
{
    public partial class Category
    {
        #region Properties
        public Boolean IsParent
        {
            get
            {
                VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
                int[] t = (from cate in _db.Categories
                           join cateSubCate in _db.CategorySubCategories on cate.ID equals cateSubCate.ID_SubCategory
                           group cate by cate.ID into cates
                           select cates.Key).ToArray();
                var v = _db.Categories.Where(p => !t.Contains(p.ID) && p.ID == this.ID);
                if (v.Count() != 0)
                    return true;
                return false;
            }
        }

        public List<Category> Children
        {
            get
            {
                if (IsParent)
                    return this.GetListAvailableChildren();
                return null;
            }
        }

        public User User
        {
            get
            {
                return this.GetUser();
            }
        }

        public List<Category> Parents
        {
            get
            {
                return this.GetAvailableParentCategories();
            }
        }

        public List<Image> Images
        {
            get
            {
                return this.GetListAvailableImage();
            }
        }

        public List<DetailCategory> Details
        {
            get
            {
                return this.GetDetails();
            }
        }

        public Status Status
        {
            get
            {
                return this.GetStatus();
            }
        }

        public City City
        {
            get
            {
                return this.GetCity();
            }
        }
        #endregion

        #region Methods
        public City GetCity()
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            var c = _db.Cities.Where(p => p.ID == this.ID_City).ToList();
            if (c.Count == 1)
                return c.First();
            return null;
        }

        public List<Category> GetAvailableParentCategories()
        {
            if (IsParent)
                return null;
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            int[] t = (from cate in _db.Categories
                       join cateSubCate in _db.CategorySubCategories on cate.ID equals cateSubCate.ID_Category
                       where cateSubCate.ID_SubCategory == this.ID && cate.ID_Status == 1 && cateSubCate.ID_Status == 7
                       group cate by cate.ID into cates
                       select cates.Key).ToArray();
            var v = _db.Categories.Where(p => t.Contains(p.ID));
            if (v.Count() == 0)
                return null;
            return v.ToList();
        }

        public List<Category> GetAvailableNotParentCategories()
        {
            if (IsParent)
                return null;
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            int[] t = (from cate in _db.Categories
                       join cateSubCate in _db.CategorySubCategories on cate.ID equals cateSubCate.ID_Category
                       where cateSubCate.ID_SubCategory == this.ID && cate.ID_Status == 1 && cateSubCate.ID_Status == 7
                       group cate by cate.ID into cates
                       select cates.Key).ToArray();
            int[] u = (from cate in _db.Categories
                       join cateSubCate in _db.CategorySubCategories on cate.ID equals cateSubCate.ID_Category
                       where cate.ID_Status == 1
                       group cate by cate.ID into cates
                       select cates.Key).ToArray();
            var v = _db.Categories.Where(p => !t.Contains(p.ID) && u.Contains(p.ID));
            if (v.Count() == 0)
                return null;
            return v.ToList();
        }

        public User GetUser()
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            var v = _db.Users.Where(p => p.ID == this.ID_User);
            if (v.Count() == 0)
                return null;
            return v.First();
        }

        public List<Image> GetListAvailableImage()
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            var v = from images in _db.Images
                    where images.ID_Category == this.ID && images.ID_Status == 1
                    select images;
            if (v.Count() == 0)
                return null;
            return v.ToList();
        }

        public List<Image> GetListNotAvailableImage()
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            var v = from images in _db.Images
                    where images.ID_Category == this.ID && images.ID_Status != 1
                    select images;
            if (v.Count() == 0)
                return null;
            return v.ToList();
        }

        public List<Category> GetListAvailableChildren()
        {
            return Category.GetListAvailableSubCategories(this.ID);
        }

        public List<Category> GetListNotAvailableChildren()
        {
            return Category.GetListNotAvailableSubCategories(this.ID);
        }

        public List<ErrorItem> SetPrimaryImage(System.Data.Linq.Binary image, int id_User, int id_Lang)
        {
            List<ErrorItem> errorList = new List<ErrorItem>();

            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();

            int errorMess = _db.ChangePrimaryImageForCategory(id_User, image, this.PrimaryImage, this.ID, 0, 0);

            if (errorMess != 1) errorList.Add(new ErrorItem("ErrorMessage", _db.ErrorMessages.Single(p => p.ID_ErrorMessage == errorMess && p.ID_Language == id_Lang).Description));

            return errorList;
        }

        public List<ErrorItem> SetPrimaryImage(System.Data.Linq.Binary image, int id_User, int id_Lang, int id_Log)
        {
            List<ErrorItem> errorList = new List<ErrorItem>();

            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();

            int errorMess = _db.ChangePrimaryImageForCategory(id_User, image, null, this.ID, id_Log, 1);

            if (errorMess != 1) errorList.Add(new ErrorItem("ErrorMessage", _db.ErrorMessages.Single(p => p.ID_ErrorMessage == errorMess && p.ID_Language == id_Lang).Description));

            return errorList;
        }

        public List<ErrorItem> InsertImage(int id_Image, int id_User, int id_Lang)
        {
            List<ErrorItem> errorList = new List<ErrorItem>();

            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();

            int errorMess = _db.InsertImageToCategory(id_User, id_Image, this.ID, 0, 0);

            if (errorMess != 1) errorList.Add(new ErrorItem("ErrorMessage", _db.ErrorMessages.Single(p => p.ID_ErrorMessage == errorMess && p.ID_Language == id_Lang).Description));

            return errorList;
        }

        public List<ErrorItem> InsertImage(int id_Image, int id_User, int id_Lang, int id_Log)
        {
            List<ErrorItem> errorList = new List<ErrorItem>();

            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();

            int errorMess = _db.InsertImageToCategory(id_User, id_Image, this.ID, id_Log, 1);

            if (errorMess != 1) errorList.Add(new ErrorItem("ErrorMessage", _db.ErrorMessages.Single(p => p.ID_ErrorMessage == errorMess && p.ID_Language == id_Lang).Description));

            return errorList;
        }

        public List<ErrorItem> RemoveImage(int id_Image, int id_User, int id_Lang)
        {
            List<ErrorItem> errorList = new List<ErrorItem>();

            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();

            int errorMess = _db.RemoveImageFromCategory(id_User, id_Image, this.ID, 0, 0);

            if (errorMess != 1) errorList.Add(new ErrorItem("ErrorMessage", _db.ErrorMessages.Single(p => p.ID_ErrorMessage == errorMess && p.ID_Language == id_Lang).Description));

            return errorList;
        }

        public List<ErrorItem> RemoveImage(int id_Image, int id_User, int id_Lang, int id_Log)
        {
            List<ErrorItem> errorList = new List<ErrorItem>();

            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();

            int errorMess = _db.RemoveImageFromCategory(id_User, id_Image, this.ID, id_Log, 1);

            if (errorMess != 1) errorList.Add(new ErrorItem("ErrorMessage", _db.ErrorMessages.Single(p => p.ID_ErrorMessage == errorMess && p.ID_Language == id_Lang).Description));

            return errorList;
        }

        public DetailCategory GetDetail(int lang)
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            var v = from dcs in _db.DetailCategories
                    where dcs.ID_Language == lang && dcs.ID_Category == this.ID
                    select dcs;
            if (v.Count() == 0)
                return null;
            return v.First();
        }

        public List<DetailCategory> GetDetails()
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            var v = from dcs in _db.DetailCategories
                    where dcs.ID_Category == this.ID
                    select dcs;
            if (v.Count() == 0)
                return null;
            return v.ToList();
        }

        public Status GetStatus()
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            var v = from Status in _db.Status
                    where Status.ID == this.ID_Status
                    select Status;
            if (v.Count() == 0)
                return null;
            return v.First();
        }

        public List<ErrorItem> InsertSubCategory(int id_User, int id_SubCategory, int id_Lang)
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            List<ErrorItem> errorList = new List<ErrorItem>();
            if (errorList.Count == 0)
            {
                int errorMess = _db.InsertSubCategoryToCategory(id_User, id_SubCategory, this.ID, 0, 0);

                if (errorMess != 1) errorList.Add(new ErrorItem("ErrorMessage", _db.ErrorMessages.Single(p => p.ID_ErrorMessage == errorMess && p.ID_Language == id_Lang).Description));
            }
            return errorList;
        }

        public List<ErrorItem> RemoveSubCategory(int id_User, int id_SubCategory, int id_Lang)
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            List<ErrorItem> errorList = new List<ErrorItem>();
            if (errorList.Count == 0)
            {
                int errorMess = _db.RemoveSubCategoryFromCategory(id_User, id_SubCategory, this.ID, 0, 0);

                if (errorMess != 1) errorList.Add(new ErrorItem("ErrorMessage", _db.ErrorMessages.Single(p => p.ID_ErrorMessage == errorMess && p.ID_Language == id_Lang).Description));
            }
            return errorList;
        }
        #endregion

        #region Static Methods
        public static Category GetCategory(int id)
        {
            //Get Category by id
            Category cat = new Category();
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            var c = _db.Categories.Where(p => p.ID == id).ToList();
            if (c.Count == 1)
                cat = c.First();
            return cat;
        }

        public static Category Parse(System.Object value)
        {
            Category temp = null;
            try
            {
                temp = (Category)value;
            }
            catch (Exception ex)
            {
            }
            return temp;
        }

        public static List<Category> GetListParentCategories()
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            int[] t = (from cate in _db.Categories
                    join cateSubCate in _db.CategorySubCategories on cate.ID equals cateSubCate.ID_SubCategory
                    group cate by cate.ID into cates
                    select cates.Key).ToArray();
            var v = _db.Categories.Where(p => !t.Contains(p.ID));
            if (v.Count() == 0)
                return null;
            return v.ToList();
        }

        public static List<Category> GetListSubCategories(int id_Category)
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            int[] t = (from cate in _db.Categories
                       join cateSubCate in _db.CategorySubCategories on cate.ID equals cateSubCate.ID_SubCategory
                       where cateSubCate.ID_Category == id_Category && cateSubCate.ID_Status == 7
                       group cate by cate.ID into cates
                       select cates.Key).ToArray();
            var v = _db.Categories.Where(p => t.Contains(p.ID));
            if (v.Count() == 0)
                return null;
            return v.ToList();
        }

        public static List<Category> GetListAvailableParentCategories()
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            int[] t = (from cate in _db.Categories
                       join cateSubCate in _db.CategorySubCategories on cate.ID equals cateSubCate.ID_SubCategory
                       group cate by cate.ID into cates
                       select cates.Key).ToArray();
            var v = _db.Categories.Where(p => !t.Contains(p.ID) && p.ID_Status == 1);
            if (v.Count() == 0)
                return null;
            return v.ToList();
        }

        public static List<Category> GetListAvailableSubCategories()
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            int[] t = (from cate in _db.Categories
                       join cateSubCate in _db.CategorySubCategories on cate.ID equals cateSubCate.ID_SubCategory
                       where cate.ID_Status == 1 && cateSubCate.ID_Status == 7
                       group cate by cate.ID into cates
                       select cates.Key).ToArray();
            var v = _db.Categories.Where(p => t.Contains(p.ID));
            if (v.Count() == 0)
                return null;
            return v.ToList();
        }

        public static List<Category> GetListAvailableSubCategories(int id_Category)
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            int[] t = (from cate in _db.Categories
                       join cateSubCate in _db.CategorySubCategories on cate.ID equals cateSubCate.ID_SubCategory
                       where cate.ID_Status == 1 && cateSubCate.ID_Category == id_Category && cateSubCate.ID_Status == 7
                       group cate by cate.ID into cates
                       select cates.Key).ToArray();
            var v = _db.Categories.Where(p => t.Contains(p.ID));
            if (v.Count() == 0)
                return null;
            return v.ToList();
        }

        public static List<Category> GetListAvailableSubCategories(int id_User, int id_Category)
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            int[] t = (from cate in _db.Categories
                       join cateSubCate in _db.CategorySubCategories on cate.ID equals cateSubCate.ID_SubCategory
                       where cate.ID_Status == 1 && cate.ID_User == id_User && cateSubCate.ID_Category == id_Category && cateSubCate.ID_Status == 7
                       group cate by cate.ID into cates
                       select cates.Key).ToArray();
            var v = _db.Categories.Where(p => t.Contains(p.ID));
            if (v.Count() == 0)
                return null;
            return v.ToList();
        }

        public static List<Category> GetListNotAvailableParentCategories()
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            int[] t = (from cate in _db.Categories
                       join cateSubCate in _db.CategorySubCategories on cate.ID equals cateSubCate.ID_SubCategory
                       group cate by cate.ID into cates
                       select cates.Key).ToArray();
            var v = _db.Categories.Where(p => !t.Contains(p.ID) && p.ID_Status != 1);
            if (v.Count() == 0)
                return null;
            return v.ToList();
        }

        public static List<Category> GetListNotAvailableSubCategories()
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            int[] t = (from cate in _db.Categories
                       join cateSubCate in _db.CategorySubCategories on cate.ID equals cateSubCate.ID_SubCategory
                       where cate.ID_Status != 1 && cateSubCate.ID_Status == 7
                       group cate by cate.ID into cates
                       select cates.Key).ToArray();
            var v = _db.Categories.Where(p => t.Contains(p.ID));
            if (v.Count() == 0)
                return null;
            return v.ToList();
        }

        public static List<Category> GetListNotAvailableSubCategories(int id_Category)
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            int[] t = (from cate in _db.Categories
                       join cateSubCate in _db.CategorySubCategories on cate.ID equals cateSubCate.ID_SubCategory
                       where cate.ID_Status != 1 && cateSubCate.ID_Category == id_Category && cateSubCate.ID_Status == 7
                       group cate by cate.ID into cates
                       select cates.Key).ToArray();
            var v = _db.Categories.Where(p => t.Contains(p.ID));
            if (v.Count() == 0)
                return null;
            return v.ToList();
        }

        public static List<Category> GetList()
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            var v = _db.Categories.Where(p => p.ID_Status != 2);
            if (v.Count() == 0)
                return null;
            return v.ToList();
        }

        public static List<Category> GetListNotAvailable()
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            return _db.Categories.Where(p => p.ID_Status != 1).ToList<Category>();
        }

        private static List<ErrorItem> InputInfomationValidation(Category category)
        {
            List<ErrorItem> errorList = new List<ErrorItem>();
            if (category.Author == "")
                errorList.Add(new ErrorItem("Author", "Author is required"));
            return errorList;
        }

        public static List<ErrorItem> Insert(ref Category ii, int id_User, int id_Lang)
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            List<ErrorItem> errorList = InputInfomationValidation(ii);
            if (errorList.Count == 0)
            {
                Nullable<Int32> id_Category = new int();
                int errorMess = _db.InsertCategory(ref id_Category, id_User, ii.TakenTime, ii.ID_City, ii.Author);
                if (errorMess == 1)
                {
                    ii.ID = id_Category.Value;
                }

                if (errorMess != 1) errorList.Add(new ErrorItem("ErrorMessage", _db.ErrorMessages.Single(p => p.ID_ErrorMessage == errorMess && p.ID_Language == id_Lang).Description));
            }
            return errorList;
        }

        public static List<ErrorItem> ChangeInformation(Category ic, int id_User, int id_Lang)
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            List<ErrorItem> errorList = InputInfomationValidation(ic);
            if (errorList.Count == 0)
            {
                Category oi = _db.Categories.Single(p => p.ID == ic.ID);
                String content = "";
                content = oi.ID_Status + "_" + oi.ID_User + "_" + oi.ID_City + "_" + oi.Author + "_" + oi.TakenTime.ToString();
                int errorMess = _db.ChangeCategory(id_User, ic.ID, ic.ID_City, ic.ID_Status, ic.TakenTime, ic.Author, ic.PrimaryImage, content, 0, 0);

                if (errorMess != 1) errorList.Add(new ErrorItem("ErrorMessage", _db.ErrorMessages.Single(p => p.ID_ErrorMessage == errorMess && p.ID_Language == id_Lang).Description));
            }
            return errorList;
        }

        public static List<ErrorItem> ChangeInformation(Category ic, int id_User, int id_Log, int id_Lang)
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            List<ErrorItem> errorList = InputInfomationValidation(ic);
            if (errorList.Count == 0)
            {
                Category oi = _db.Categories.Single(p => p.ID == ic.ID);
                int errorMess = _db.ChangeCategory(id_User, ic.ID, ic.ID_City, ic.ID_Status, ic.TakenTime, ic.Author, ic.PrimaryImage, "", id_Log, 1);

                if (errorMess != 1) errorList.Add(new ErrorItem("ErrorMessage", _db.ErrorMessages.Single(p => p.ID_ErrorMessage == errorMess && p.ID_Language == id_Lang).Description));
            }
            return errorList;
        }

        public static List<ErrorItem> Delete(int id_Category, int id_User, int deep, int id_Lang)
        {
            List<ErrorItem> errorList = new List<ErrorItem>();
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();

            int errorMess = _db.DeleteCategory(id_User, id_Category, deep);

            if (errorMess != 1) errorList.Add(new ErrorItem("ErrorMessage", _db.ErrorMessages.Single(p => p.ID_ErrorMessage == errorMess && p.ID_Language == id_Lang).Description));

            return errorList;
        }

        public static Boolean Existed(String categoryName, int id_Lang){
            VietImageDataClassesDataContext context = new VietImageDataClassesDataContext();

            var check = from cates in context.Categories
                        join decates in context.DetailCategories on cates.ID equals decates.ID_Category
                        where decates.Name.ToLower() == categoryName.ToLower() && decates.ID_Language == id_Lang
                        select cates;

            if (check.Count() != 0)
                return true;
            return false;
        }
        #endregion

        #region Search

        private static String BuildQuery(Dictionary<String, System.Object> parametersList, Dictionary<String, bool> exactList)
        {
            String query = "";

            DateTime noValue = new DateTime(2000, 1, 1);

            foreach (KeyValuePair<String, System.Object> kvp in parametersList)
            {
                if (query.Length != 0)
                    query += " AND ";
                if (kvp.Key == "ID" || kvp.Key == "ID_ParentCategory" || kvp.Key == "ID_Author" || kvp.Key == "ID_Status" || kvp.Key == "ID_User")
                {
                    if (int.Parse(kvp.Value.ToString()) >= 1)
                        query += kvp.Key + " = " + int.Parse(kvp.Value.ToString());
                }
                else if (kvp.Key == "CreatedTime")
                {
                    if (DateTime.Parse(kvp.Value.ToString()) != noValue)
                        query += "CreatedTime" + " = " + DateTime.Parse(kvp.Value.ToString()).ToString("yyyy-MM-dd") + "";
                }
            }

            if (parametersList.ContainsKey("CreatedbeginDate") && parametersList.ContainsKey("CreatedendDate"))
            {
                query += " DATEDIFF(day, " + DateTime.Parse(parametersList["CreatedendDate"].ToString()).ToString("yyyy-MM-dd") + " , CreatedTime) >= 0";
                query += " DATEDIFF(day, CreatedTime , " + DateTime.Parse(parametersList["CreatedbeginDate"].ToString()).ToString("yyyy-MM-dd") + ") >= 0";
            }

            return query;
        }

        public static System.Object GetCategory(Dictionary<String, System.Object> parametersList, Dictionary<String, bool> exactList, bool oneRow)
        {
            VietImageDataClassesDataContext _db = new VietImageDataClassesDataContext();
            String query = BuildQuery(parametersList, exactList);
            var v = _db.ExecuteQuery<Category>(query);
            if (oneRow)
            {
                if (v.Count() == 0)
                    return null;
                return v.First();
            }
            if (v.Count() == 0)
                return null;
            return v.OrderByDescending(p => p.ID).ToList();
        }

        #endregion
    }
}